const db = require('../../db')

/**
 * POST 单位的回调函数
 * @param warehouseName  单位名称
 * @param warehousecode  单位代码
 * @param warehouseType  类型
 * @param remark  备注
 */
exports.add = (req, res) => {
  // 接收表单的数据
  const warehouseInfo = req.body
  let sql = 'select * from account_warehouse where warehouseName=?'
  db.query(sql, [warehouseInfo.warehouseName], (error, result) => {
    if (error) {
      return res.cc(error)
    }
    if (result.length > 0) {
      return res.cc('仓库名称已被占用!')
    }
    // 定义 SQL 语句
    let sql1 = 'insert into account_warehouse set ?'
    db.query(sql1, {
      warehouseName: warehouseInfo.warehouseName,
      warehouseAddress: warehouseInfo.warehouseAddress,
      unitId: warehouseInfo.unitId,
      unitName: warehouseInfo.unitName,
      createdTime: Date.now(),
      remark: warehouseInfo.remark
    }, (error, result) => {
      if (error) return res.cc(error)
      // 判断影响行数是否为 1
      if (result.affectedRows !== 1) return res.cc('新增仓库失败！')
      return res.cc('新增仓库成功', true)
    })
  })
}

exports.del = (req, res) => {
  // 接收表单的数据
  const warehouseInfo = req.body
  let sql = 'select * from account_warehouse where id=?'
  db.query(sql, [warehouseInfo.id], (error, result) => {
    if (error) {
      return res.cc(error)
    }
    if (result.length === 0) {
      return res.cc('未找到该仓库!')
    }
    // 定义 SQL 语句
    const sql1 = 'delete from account_warehouse where id=?'
    // 调用 db.query() 执行 sql 语句
    db.query(sql1, warehouseInfo.id, (error, result) => {
      if (error) return res.cc(error)
      // 判断影响行数是否为 1
      if (result.affectedRows !== 1) return res.cc('删除仓库失败！')
      return res.cc('删除仓库成功', true)
    })
  })
}

exports.update = (req, res) => {
  // 接收表单的数据
  const warehouseInfo = req.body
  let sql = 'select * from account_warehouse where id=?'
  db.query(sql, [warehouseInfo.id], (error, result) => {
    if (error) {
      return res.cc(error)
    }
    if (result.length === 0) {
      return res.cc('未找到该仓库!')
    }
    // 更新仓库信息
    const updateSQL = "UPDATE account_warehouse SET warehouseName=?, remark=?, warehouseAddress=?, unitId=?, unitName=? WHERE id=?";
    const updateValues = [
      warehouseInfo.warehouseName,
      warehouseInfo.remark,
      warehouseInfo.warehouseAddress,
      warehouseInfo.unitId,
      warehouseInfo.unitName,
      warehouseInfo.id,
    ];

    db.query(updateSQL, updateValues, (error, result) => {
      if (error) {
        return res.cc(error);
      }

      if (result.affectedRows !== 1) {
        return res.cc('修改仓库失败！');
      }

      return res.cc('修改仓库成功', true);
    });
  })
}

exports.allList = (req, res) => {
  const page = req.body.page ? parseInt(req.body.page) : 1;
  const limit = req.body.limit ? parseInt(req.body.limit) : 10;
  const warehouseName = req.body.warehouseName || '';
  const unitId = req.body.unitId || '';
  const sqlCount = "SELECT COUNT(*) AS total FROM account_warehouse";

  db.query(sqlCount, (error, countResults) => {
    if (error) {
      return res.cc(error);
    }

    const total = countResults[0].total;
    const offset = (page - 1) * limit;
    let sql;
    let params;

    if (warehouseName && unitId) {
      sql = `SELECT * FROM account_warehouse WHERE warehouseName LIKE ? AND unitId = ? LIMIT ? OFFSET ?`;
      params = [`%${ warehouseName }%`, unitId, limit, offset];
    } else if (warehouseName) {
      sql = `SELECT * FROM account_warehouse WHERE warehouseName LIKE ? LIMIT ? OFFSET ?`;
      params = [`%${ warehouseName }%`, limit, offset];
    } else if (unitId) {
      sql = `SELECT * FROM account_warehouse WHERE unitId = ? LIMIT ? OFFSET ?`;
      params = [unitId, limit, offset];
    } else {
      sql = `SELECT * FROM account_warehouse LIMIT ? OFFSET ?`;
      params = [limit, offset];
    }

    db.query(sql, params, (error, results) => {
      if (error) {
        return res.cc(error);
      }
      // 查询仓库管理员信息
      const adminSQL = 'SELECT * FROM account_warehouse_man WHERE warehouseId = ?';
      const resultPromises = results.map(async warehouse => {
        const admins = await new Promise((resolve, reject) => {
          db.query(adminSQL, [warehouse.id], (error, adminResults) => {
            if (error) {
              reject(error);
            } else {
              // resolve(adminResults);
              const adminNames = adminResults.map(admin => admin.userName).join(', ');
              resolve(adminNames);
            }
          });
        });
        return { ...warehouse, admins };
      });

      // 等待所有仓库管理员信息查询完成
      Promise.all(resultPromises)
         .then(resultsWithAdmins => {
           return res.cc('获取仓库成功', true, resultsWithAdmins, {
             total,
             page,
             limit
           });
         })
         .catch(error => {
           return res.cc(error);
         });
    });
  });
}


// 查询仓库管理员列表
exports.getAdmins = (req, res) => {
  const { warehouseId } = req.body;
  const getAdminsSQL = 'SELECT * FROM account_warehouse_man WHERE warehouseId = ?';
  db.query(getAdminsSQL, [warehouseId], (error, results) => {
    if (error) {
      return res.cc(error);
    }
    return res.cc('查询管理员列表成功', true, results);
  });
}

// 根据人查仓库
exports.manWithWarehouse = (req, res) => {
  const userInfo = req.headers['userinfo'];
  const decodedUserInfo = decodeURIComponent(userInfo);
  const userInfoObject = JSON.parse(decodedUserInfo);

  if (userInfoObject.role === '1') {
    // 如果是管理员，直接查询 account_warehouse 返回全部数据
    const sql = "SELECT * FROM account_warehouse";
    db.query(sql, (error, results) => {
      if (error) {
        return res.cc(error);
      }
      return res.cc('获取仓库列表成功', true, results);
    });
  } else {
    // 如果不是管理员，根据 userId 查询 account_warehouse_man 表
    const sql = "SELECT * FROM account_warehouse_man WHERE userId = ?";
    db.query(sql, [userInfoObject.userId], (error, results) => {
      console.log('this1----',results)
      if (error) {
        return res.cc(error);
      }
      return res.cc('获取仓库列表成功', true, results);
    });
  }
};


exports.setAdmins = (req, res) => {
  // 新增/编辑仓库管理员
  const warehouseInfo = req.body;
  const managerList = warehouseInfo.managerList
  const warehouseId = warehouseInfo.warehouseId;

  // Get the list of user IDs in the request
  const userIdsInRequest = managerList.map(admin => admin.userId);

  // Retrieve the current administrators from the database
  const getCurrentAdminsSQL = 'SELECT * FROM account_warehouse_man WHERE warehouseId = ?';
  db.query(getCurrentAdminsSQL, [warehouseId], (error, currentAdmins) => {
    if (error) {
      return res.cc(error);
    }

    // Extract the user IDs of the current administrators
    const currentAdminUserIds = currentAdmins.map(admin => admin.userId);

    // Identify the administrators to be removed
    const adminsToRemove = currentAdminUserIds.filter(userId => !userIdsInRequest.includes(userId));

    // Remove the administrators that are no longer in the request
    if (adminsToRemove.length > 0) {
      const deleteAdminSQL = 'DELETE FROM account_warehouse_man WHERE warehouseId = ? AND userId IN (?)';
      db.query(deleteAdminSQL, [warehouseId, adminsToRemove], (error, result) => {
        if (error) {
          return res.cc(error);
        }
      });
    }

    // Iterate through the administrators in the request
    managerList.forEach(admin => {
      // Check if the administrator already exists in the database
      const existingAdmin = currentAdmins.find(a => a.userId === admin.userId);

      if (existingAdmin) {
        // Update the existing administrator's information
        const updateAdminSQL = 'UPDATE account_warehouse_man SET userName = ?, createdTime = ?, warehouseName=? WHERE warehouseId = ? AND userId = ?';
        db.query(updateAdminSQL, [admin.userName, Date.now(), admin.warehouseName, warehouseId, admin.userId], (error, result) => {
          if (error) {
            return res.cc(error);
          }
        });
      } else {
        // Add a new administrator
        const insertAdminSQL = 'INSERT INTO account_warehouse_man (warehouseId, warehouseName, userId, userName, createdTime) VALUES (?,?,?,?,?)';
        db.query(insertAdminSQL, [warehouseId, admin.warehouseName, admin.userId, admin.userName, Date.now()], (error, result) => {
          if (error) {
            return res.cc(error);
          }
        });
      }
    });

    return res.cc('设置管理员成功', true);
  });
};


exports.storeList = (req, res) => {
  const page = req.body.page ? parseInt(req.body.page) : 1; // Get the page number, default to the first page
  const limit = req.body.limit ? parseInt(req.body.limit) : 10; // Get the number of items per page, default to 10
  const warehouseId = req.body.warehouseId || ''; // Get warehouseName from request body or set to an empty string
  const inBoundName = req.body.inBoundName || '';
  const offset = (page - 1) * limit;
  // 查询数据
  const dataSql = `
    SELECT 
    inBound.inBoundName AS goodName,
    inBound.inBoundType AS goodType,
    inBound.inBoundUnitName AS goodUnitName,
    inBound.inBoundPrice AS goodPrice,
    SUM(inBound.inBoundNum) - IFNULL(SUM(outBound.outBoundNum), 0) AS goodNum
FROM
    (SELECT inBoundName, inBoundType, inBoundUnitName, inBoundPrice, SUM(inBoundNum) AS inBoundNum
     FROM account_in_bound_detail
     WHERE warehouseId = ? 
       AND inBoundName LIKE ?
     GROUP BY inBoundName, inBoundType, inBoundUnitName, inBoundPrice) inBound
LEFT JOIN
    (SELECT outBoundName, outBoundType, SUM(outBoundNum) AS outBoundNum
     FROM account_out_bound_detail
     WHERE warehouseId =?
     GROUP BY outBoundName, outBoundType) outBound
ON inBound.inBoundName = outBound.outBoundName AND inBound.inBoundType = outBound.outBoundType
GROUP BY inBound.inBoundName, inBound.inBoundType, inBound.inBoundUnitName, inBound.inBoundPrice
LIMIT ? OFFSET ?;
  `;

  // 查询总行数
  const countSql = `
    SELECT COUNT(*) AS totalCount
    FROM
      (SELECT inBoundName
       FROM account_in_bound_detail
       WHERE warehouseId = ? 
         AND inBoundName LIKE ?  
       GROUP BY inBoundName) inBound;
  `;
  const values = [warehouseId, `%${ inBoundName }%`, warehouseId, limit, offset];
  // 执行数据查询
  db.query(dataSql, values, (error, results) => {
    if (error) {
      return res.cc(error);
    }
    // 查询总行数
    db.query(countSql, [warehouseId, `%${ inBoundName }%`], (error, countResult) => {
      if (error) {
        return res.cc(error);
      }
      const total = countResult[0].totalCount;
      return res.cc('获取物品列表成功', true, results, {
        total,
        page,
        limit
      });
    });
  });
}

